﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Data.SqlTypes ;

namespace TestJQGrid
{
    public class GlobalMethods
    {
        public GlobalMethods() { }

        private static string GetConnectionString()
        {
            string _connectionStringsName = "AgentBillingConnectionString";

            System.Configuration.ConnectionStringSettingsCollection config = System.Configuration.ConfigurationManager.ConnectionStrings;
            for (int i = 0; i < config.Count; i++)
            {
                if (config[i].Name.Equals(_connectionStringsName, StringComparison.OrdinalIgnoreCase))
                    return config[i].ToString();
            }
            return String.Empty;
        }

        public static DataTable ExecuteSelectQuery(string query)
        {
            //open connection
            SqlConnection conn = new SqlConnection(GetConnectionString());
            conn.Open();

            //create command
            SqlCommand command = new SqlCommand(query, conn);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = command;

            //retireve data
            DataTable dt = new DataTable();

            //retrieve data
            da.Fill(dt);


            //return the result
            conn.Close();
            return dt;
        }

        public static int ExecuteNonQuery(string query)
        {
            //open connection
            SqlConnection conn = new SqlConnection(GetConnectionString());
            conn.Open();
            // command
            SqlCommand command = new SqlCommand(query, conn);

            //retrieve data
            int result = command.ExecuteNonQuery();

            //return the result
            conn.Close();
            return result;
        }

        public static string ExecuteScalar(string query)
        {
            //open connection
            SqlConnection conn = new SqlConnection(GetConnectionString());
            conn.Open();

            //create command
            SqlCommand command = new SqlCommand(query, conn);
            string returnValue;
            try
            {
                returnValue = command.ExecuteScalar().ToString();
            }
            catch (NullReferenceException)
            {
                return "";
            }

            //return the result
            conn.Close();
            return returnValue;
        }

        public static int ExecuteIdentityInsert(string query, string idColumn)
        {
            //open connection
            SqlConnection conn = new SqlConnection(GetConnectionString());
            conn.Open();

            //create id parameter
            idColumn = "@" + idColumn;
            SqlParameter idparam = new SqlParameter(idColumn, SqlDbType.Int);
            idparam.Direction = ParameterDirection.Output;

            //add identity part
            query += " SET " + idColumn + " = SCOPE_IDENTITY()";

            //create command
            SqlCommand command = new SqlCommand(query, conn);
            command.Parameters.Add(idparam);

            command.ExecuteScalar();

            return ((int)idparam.Value);
        }

        public static void uploadDT2DB(DataTable dt, string TableName,bool NeedsIdentity=false)
        {
            // Add identity column to Datatable
            if (NeedsIdentity)
            {
                DataColumn dc = dt.Columns.Add("IndexID", System.Type.GetType("System.Int32"));
                dc.SetOrdinal(0);
            }

            //upload datatable to db
            SqlConnection conn = new SqlConnection(GetConnectionString());
            conn.Open();

            //open bulnk copy
            SqlBulkCopy sbc = new SqlBulkCopy(conn);

            //perform update
            sbc.DestinationTableName = TableName;
            sbc.WriteToServer(dt);

            //close
            conn.Close();
            return;
        }
    }
}